# -*- coding: utf-8 -*-

import pandas as pd
import sqlite3
import datetime
import tushare as ts
import matplotlib.pyplot as plt
 
 

if __name__ == '__main__':  
    diff_days=300
    rdate =  datetime.datetime.now() - datetime.timedelta(days=diff_days)
    start_dt= rdate.strftime('%Y-%m-%d') # %Y-%m-%d %H:%M:%S
    end_dt= datetime.datetime.now() .strftime('%Y-%m-%d') # %Y-%m-%d %H:%M:%S
    dbpath='/home/xzh/data/stockdata2018.db'
    conn = sqlite3.connect(dbpath) 
    sql="select * from top10_holder where date(rpt_dt)>='{start_dt}'  and date(rpt_dt)<='{end_dt}' and stock_cd not like '688%'   "
    st= pd.read_sql(sql.format(start_dt=start_dt,end_dt=end_dt),con= conn)
    #not_list=['香港中央结算有限公司','香港中央结算(代理人)有限(2)公司','香港中央结算(代理人)有限公司','香港中央结算有限公']
    # HKSCC NOMINEES LIMITED(香港中央结算(代理人)有限公司)
    st=st[~st['holder_nm'].str.contains(r'HKSCC|香港中央结算')]
    #st.query("holder_nm.str.contains('HKSCC')")
    st['rpt_dt']=pd.to_datetime(st['rpt_dt'])
    st=st[st['rpt_dt'].apply(lambda x: x.day>=30 and x.month in(3,6,9,12))]
    st['rpt_dt']=st['rpt_dt'].apply(lambda x:str(x)[0:10])
    st=pd.pivot_table(st,values='hold_rate',index=['stock_cd','holder_nm'],columns=['rpt_dt'],aggfunc=['max'],fill_value=0)
    rpt=list(st.columns.levels[1])
    min_rpt=min(rpt)
    max_rpt=max(rpt)
    st.columns=st.columns.levels[1]
    st.reset_index(inplace=True)
    st['add_hold']=st[max_rpt]-st[min_rpt]
    st=st[st['add_hold']>0.5]
    new_stock=st.groupby('stock_cd')['add_hold'].sum()
    new_stock=new_stock[new_stock>60]
    st=st[~st['stock_cd'].isin(new_stock.index)]
    guoqi=eval(open('../国企.txt','r') .read())#pickle.load(open('stock/国企.plk','rb'))
    st['国企']=st['stock_cd'].apply(lambda x: 'Y' if x in guoqi.keys() else '')
    if 'stocklist' not in dir():
        stocklist=ts.get_stock_basics()
        del stocklist['area'],stocklist['pb']
    st=st.merge(stocklist,left_on='stock_cd',right_index=True)
    

    
#    o.columns.names=['func','date']
#    o=o.swaplevel('func','date',axis=1)
#    o.sort_index(axis=1,level=0,inplace=True)
 
     
  
